DROP TABLE employee;
CREATE TABLE employee(
  id NUMBER NOT NULL,
  ename VARCHAR2(12) NOT NULL,
  sex NUMBER NOT NULL,
  birthday DATE NOT NULL,
  join_date DATE NOT NULL,
  create_date DATE NOT NULL,
  edit_date DATE,
  eno VARCHAR2(12) NOT NULL
);


--添加主键
ALTER TABLE employee ADD PRIMARY KEY (id);

--添加注释
comment on column employee.id is '主键';
comment on column employee.ename is '姓名';
comment on column employee.sex is '性别(0-男,1-女)';
comment on column employee.birthday is '出生日期';
comment on column employee.join_date is '入职时间';
comment on column employee.create_date is '创建日期';
comment on column employee.edit_date is '修改日期';
comment on column employee.eno is '员工编号';

-- 创建自增序列
CREATE SEQUENCE employee_sequence
increment by 1                  -- 每次加几个
start with 1                    -- 从1开始计数
nomaxvalue                      -- 不设置最大值
nocycle                         -- 一直累加，不循环
nocache                        -- 不建缓冲区

-- 创建出发器
create or replace trigger employee_triger
before insert on employee
for each row when (new.id is null)
  begin
    select employee_sequence.nextval into :new.id from dual;
  end;
/

INSERT INTO employee(ename, sex, birthday, join_date, eno, create_date, edit_date)
VALUES ('张三', 0, to_date('1991-01-01', 'yyyy-mm-dd'), to_date('2012-01-01', 'yyyy-mm-dd'), '001', sysdate, null);
INSERT INTO employee(ename, sex, birthday, join_date, eno, create_date, edit_date)
VALUES ('李四', 0, to_date('1991-01-02', 'yyyy-mm-dd'), to_date('2012-01-02', 'yyyy-mm-dd'), '002', sysdate, null);
INSERT INTO employee(ename, sex, birthday, join_date, eno, create_date, edit_date)
VALUES ('王五', 1, to_date('1991-01-01', 'yyyy-mm-dd'), to_date('2012-01-01', 'yyyy-mm-dd'), '003', sysdate, null);